package utils;

import java.security.NoSuchAlgorithmException;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;

import models.*;

public class DAOUser extends DAO {
	public DAOUser() throws Exception{
		super();
	}
	
	public String insertUserSQL(BeanUser u) throws SQLException, NoSuchAlgorithmException{
		Statement sentencia = connexio.createStatement();
		String user = u.getUser();
		String mail = u.getMail();
		String nombre = u.getNombre();
		String apellidos = u.getApellidos();
		String sexo = u.getSexo();
		String password = u.getPassword();
		String day = u.getDay();
		String month = u.getMonth();
		String year = u.getYear();
		String activationCode = u.generateActivationCode();
		String query = "INSERT INTO `ts1`.`user` (`user`, `nombre`, `apellidos`, `sexo`, `mail`, `password`, `data`, `activationCode`) VALUES ('"+user+ "', '"+ nombre+"', '"+apellidos+"', '"+sexo+"', '"+mail+"', '"+password+"', '"+year+"/"+month+"/"+day+"', '" + activationCode + "')";
		sentencia.execute(query);
		
		return activationCode;
	}
	
	public boolean isUserActivated(String activationCode)
	{
		boolean activated = true;
		String query = "SELECT user FROM `ts1`.`User` WHERE activationCode = '" + activationCode + "'";
		ResultSet result;
		try {
			Statement sentencia = connexio.createStatement();
			result = sentencia.executeQuery(query);
			if(result.next())
				activated = false;
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return activated;
	}
	
	public void setUserActivated(String activationCode)
	{
		String query = "UPDATE `ts1`.`User` SET activationCode = NULL WHERE activationCode = '" + activationCode + "'";
		try {
			Statement sentencia = connexio.createStatement();
			sentencia.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public boolean userExists(String user) throws SQLException{
		boolean exists = false;

			Statement sentencia = connexio.createStatement();
			String query = "SELECT user FROM `ts1`.`user` WHERE user ='" + user + "';";
			ResultSet result = sentencia.executeQuery(query);
			if(result.next())
				exists = true;

		return exists;
	}

	public boolean correctPassword(String user, String pass) throws SQLException {
		boolean correct = false;

			Statement sentencia = connexio.createStatement();
			String query = "SELECT password FROM `ts1`.`User` WHERE user ='" + user + "' AND password ='"+pass+"';";
			ResultSet result = sentencia.executeQuery(query);
			if(result.next())
				correct = true;

		return correct;
	}
	
	public boolean isAdmin(String user) throws SQLException {
		boolean admin = false;
		Statement sentencia = connexio.createStatement();
			String query = "SELECT isAdmin FROM `ts1`.`User` WHERE user ='" + user + "'";
			ResultSet result = sentencia.executeQuery(query);
			if(result.next())
				admin = result.getBoolean("isAdmin");

		return admin;
	}
	
	public User getUser(String username) throws SQLException {
		String query = "SELECT nombre, apellidos, mail FROM `ts1`.`User` WHERE user ='" + username + "' ORDER BY nombre, apellidos";
		String nombre= "";
		String email= "";
		Statement sentencia = connexio.createStatement();

		ResultSet result = sentencia.executeQuery(query);
		if(result.next())
			nombre = result.getString("nombre");
			nombre += " " + result.getString("apellidos");
			email = result.getString("mail");
	
		
		User user = new User(nombre, username);
		user.setEmail(email);
		
		return user;
	}
	
	
	public BeanProfile getProfile(String username) throws SQLException {
		BeanProfile  profile = null;
		Statement sentencia = connexio.createStatement();
		String query = "SELECT * FROM `ts1`.`User` WHERE user ='" + username + "'";
		String nombre= "";
		String apellidos= "";
		Date date;
		String llocnaix= "";
		String hobbies= "";
		String sexo= "";
		String password = "";
		
			ResultSet result = sentencia.executeQuery(query);
			if(result.next()){
				nombre =  result.getString("nombre");
				apellidos = result.getString("apellidos");
				date = result.getDate("data");
				llocnaix = result.getString("llocnaix");
				hobbies = result.getString("hobbies");
				sexo = result.getString("sexo");
				password = result.getString("password");
				Calendar calendario = Calendar.getInstance();
				calendario.setTime(date);
				String day = String.valueOf(calendario.get(Calendar.DAY_OF_MONTH));
				String month = String.valueOf(calendario.get(Calendar.MONTH));
				String year = String.valueOf(calendario.get(Calendar.YEAR));
				profile = new BeanProfile(password, nombre,apellidos,sexo, day, month, year,llocnaix,hobbies);
			}

		
		return profile;
	}
	
	public void deleteUser(String user) throws SQLException{
		String query = "DELETE FROM user WHERE user = '"+ user+"'";
		Statement sentencia = connexio.createStatement();
		sentencia.execute(query);
	}
	
	public ArrayList<User> getusers(String user, int numUsers) throws SQLException{
		ArrayList<User> users = new ArrayList<User>();
		Statement sentencia = connexio.createStatement();

			String query = "SELECT user,nombre FROM `ts1`.`user` WHERE user ='" + user + "' OR nombre ='"+ user + "' limit " + numUsers + ";";
			ResultSet result = sentencia.executeQuery(query);
			while(result.next()) {
				User tweet = new User(result.getString("nombre"),result.getString("user"));
				users.add(tweet);
			}
		
		return users;
	}

	public void setFollowers(ArrayList<User> users, String userSearch)
			throws SQLException  {

				for(int i=0; i< users.size();i++){
					setFollower(users.get(i), userSearch);
				}
				//System.out.println(users.get(i).isFollowing());
	}
	
	public void setFollower(User user, String userSearch) throws SQLException{
			Statement sentencia = connexio.createStatement();
			String query = "SELECT follower,following FROM `ts1`.`follow` WHERE follower ='" + userSearch + "' AND following ='"+ user.getUser() + "';";
			ResultSet result = sentencia.executeQuery(query);
			boolean next = result.next();
			if(next){
				user.setFollowing(true);
			}else{
				user.setFollowing(false);
			}
			result = null;
	}
	
	public void setContacts(ArrayList<User> users, String userSearch) throws SQLException  {

		for(int i=0; i< users.size();i++){
			setContact(users.get(i), userSearch);
		}
		//System.out.println(users.get(i).isFollowing());
	}
	
	public void setContact(User user, String userSearch) throws SQLException{
		Statement sentencia = connexio.createStatement();
			String query = "SELECT userContacted,userContactor FROM `ts1`.`contact` WHERE userContactor ='" + userSearch + "' AND userContacted ='"+ user.getUser() + "';";
			ResultSet result = sentencia.executeQuery(query);
			boolean next = result.next();
			if(next){
				user.setContact(true);
			}else{
				user.setContact(false);
			}
			result = null;
	
	}
	
	public void setAcceptedContact(User currentUser, String user) throws SQLException{
		Statement sentencia = connexio.createStatement();
			String query = "SELECT userContacted,userContactor FROM `ts1`.`contact` WHERE userContactor ='" + currentUser.getUser() + "' AND userContacted ='"+ user + "';";
			ResultSet result = sentencia.executeQuery(query);
			boolean next = result.next();
			if(next){
				currentUser.setAcceptedContact(true);
			}else{
				currentUser.setAcceptedContact(false);
			}
			result = null;

	}
	
	public ArrayList<User> followingUsers(String follower, int numUsers) throws SQLException{
		ArrayList<User> users = new ArrayList<User>();
		Statement sentencia = connexio.createStatement();
			String query = "SELECT user,nombre, apellidos FROM ts1.user u where u.user IN (SELECT following FROM ts1.follow where follower = '"+follower+"') ORDER BY nombre, apellidos limit " + numUsers + ";";
			ResultSet result = sentencia.executeQuery(query);
			while(result.next()) {
				User user = new User(result.getString("nombre"),result.getString("user"));
				user.setFollowing(true);
				users.add(user);
			}
		
		
		return users;
	}
	
	public ArrayList<User> followerUsers(String following, int numUsers) throws SQLException{
		ArrayList<User> users = new ArrayList<User>();
		Statement sentencia = connexio.createStatement();
			String query = "SELECT user,nombre, apellidos FROM ts1.user u where u.user IN (SELECT follower FROM ts1.follow where following = '"+following+"') ORDER BY nombre, apellidos limit " + numUsers + ";";
			ResultSet result = sentencia.executeQuery(query);
 			while(result.next()) {
				User user = new User(result.getString("nombre"),result.getString("user"));
				users.add(user);
			}
		
		
		return users;
	}
	
	public ArrayList<User> ContactUsers(String contactor, int numUsers) throws SQLException{
		ArrayList<User> users = new ArrayList<User>();
		Statement sentencia = connexio.createStatement();
			String query = "SELECT user,nombre, apellidos FROM ts1.user u where u.user IN (SELECT userContacted FROM ts1.contact where userContactor = '"+contactor+"') ORDER BY nombre, apellidos limit " + numUsers + ";";
			ResultSet result = sentencia.executeQuery(query);
 			while(result.next()) {
				User user = new User(result.getString("nombre"),result.getString("user"));
				users.add(user);
			}
		
		return users;
	}
	
	public void followUser(String follower, String following) throws SQLException{
		Statement sentencia = connexio.createStatement();
		String query = "INSERT INTO `ts1`.`follow` (`follower`, `following`) VALUES ('"+follower+ "', '"+ following+"');";
		sentencia.execute(query);
	}
	
	public void unfollowUser(String follower, String following) throws SQLException{
		Statement sentencia = connexio.createStatement();
		String query = "DELETE FROM `ts1`.`follow` WHERE follower = '"+ follower+"' AND following = '"+following+"';";
		sentencia.execute(query);
	}
	
	public void addContact(String userContacted, String userContactor) throws SQLException{
		Statement sentencia = connexio.createStatement();
		String query = "INSERT INTO `ts1`.`contact` (`userContacted`, `userContactor`) VALUES ('"+userContacted+ "', '"+ userContactor+"');";
		sentencia.execute(query);
	}
	
	public void deleteContact(String userContacted, String userContactor) throws SQLException{
		Statement sentencia = connexio.createStatement();
		String query = "DELETE FROM `ts1`.`contact` WHERE userContacted = '"+ userContacted+"' AND userContactor = '"+userContactor+"';";
		sentencia.execute(query);
	}

	public void setName(String currentUser, String newName) throws SQLException {
		Statement sentencia = connexio.createStatement();
		String query = "UPDATE `ts1`.`User` SET nombre = '" + newName+ "' WHERE user = '" + currentUser + "'";
		sentencia.execute(query);
		
	}

	public void editProfile(BeanProfile profile, String user) throws SQLException {
		Statement sentencia = connexio.createStatement();
		String date = profile.getYear()+"/"+profile.getMonth()+"/"+profile.getDay();
		String query = "UPDATE `ts1`.`User` SET nombre = '" + profile.getNombre() + 
				"', apellidos = '" + profile.getApellidos() + "'," +
				" sexo = '" + profile.getSexo() + "'," +
				" password = '" + profile.getPassword() + "'," +
				" data = '" + date + "'," +
				" llocnaix = '" + profile.getLlocnaix() + "'," +
				" hobbies = '" + profile.getHobbies() + "'" +
						" WHERE user = '" + user + "'";
		sentencia.execute(query);
	}
}
